cap program drop txls
program define txls

 version 16

syntax [anything], [todir(string) XLS]

	local pwd=c(pwd)
	local ossys=c(os) 
	if "`ossys'"!= "Windows"{
		di as red "Error: txls only used in Windows."
		*exit
	}
    if "`xls'"==""{
		local xls xlsx
	}

	local anything `anything'
	if `"`anything'"'==""{
		db txls
		exit
	}
	else{
		mata: st_local("filename2",pathbasename(`"`anything'"')) 
		local filenames = usubstr("filename2",1,strpos("filename2",".xls")) // 	remove the file extension
		tempname flag dirflag
		mata: analyzea(`"`anything'"',"`flag'")
		
		if `flag'==-1{
			di as red `"Error: file [`anything'] not found"'
			exit
		}
		if `flag'==-2{
			di as red `"Error: Directory [`anything'] not exist"'
			exit
		}
		
	}
	
	if `"`todir'"'!=""{
		cap mkdir `"`todir'"'
		mata: st_numscalar("`dirflag'", direxists(`"`todir'"'))
		if `dirflag'==0{
		    di as red "ERROR:" `"`todir'"' "not exist."
			exit
		}
	
	}
	else{
        local anything2 `anything'
		if `flag'==1{
			mata: st_local("anything2",pathgetparent(`"`anything'"')) 
			// mata: st_local("filename2",pathbasename(`"`anything'"')) 
		}
		cap mkdir `"`anything2'/`xls'"'
		local todir `"`anything2'/`xls'"'
	}

	if `flag'==1{
		mata: st_local("filetype",pathsuffix(`"`anything'"'))
		local xls= substr(`"`filetype'"',2,.) // the specified filname determines the type
    if "`xls'"=="xls"{
		local xls xlsx
	}
	else{
		local xls xls
	}
				
		
		cap erase "`pwd'\_xls__xlsx_.vbs"
		mata:writevbsfile`xls'(`"`anything'"',`"`filename2'"',`"`todir'"')
	}
	
	if `flag'==2{
		cap erase "`pwd'\_xls__xlsx_.vbs"
		mata:writevbs`xls'(`"`anything'"',`"`todir'"')
	}
	
		if "`xls'"=="xls"{
			di _n "converting *.xlsx to *.xls..."
		}
		else{
			di _n "converting *.xls to *.xlsx..."
		}
			
	

	local pwd=c(pwd)
	!"`pwd'\_xls__xlsx_.vbs"
	*cap erase "`pwd'\_xls__xlsx_.vbs"
	
 	di _n `"*.`xls' files are stored in `todir'"'
 
 end
 
 
 
 cap mata mata drop analyzea()
 cap mata mata drop writevbsxls()
 cap mata mata drop writevbsfilexls()
 cap mata mata drop writevbsxlsx()
 cap mata mata drop writevbsfilexlsx()
mata:

void function analyzea(string scalar path,string scalar flag)
{
	if( pathsuffix(path)==".xls" | pathsuffix(path)==".xlsx"){
		xflag=1
		if(!fileexists(path)){
			xflag=-1
			 if( direxists(path)) {
				xflag=2
			 }				
		}
	}
	else{
		 if( direxists(path)) {
		 	xflag=2
		 }
		 else{
		 	xflag=-2
		 }
	}
	
	st_numscalar(flag,xflag)
	
	
}





void function writevbsxlsx(string scalar path,string scalar todir)
{
	
	cc=`"""'
	writefile=fopen("_xls__xlsx_.vbs","rw")
	fwrite(writefile, sprintf(`"Set objFSO = CreateObject("Scripting.FileSystemObject")  \n"'))
	fwrite(writefile, sprintf(`"Set objExcel = CreateObject("Excel.Application")  \n"'))
	fwrite(writefile, sprintf(`"objExcel.DisplayAlerts = False  \n"'))
	fwrite(writefile, sprintf(`"strFolder = %s%s\%s  \n"',cc,path,cc))
	fwrite(writefile, sprintf(`"strFolder2 =%s%s\%s  \n"',cc,todir,cc))
	fwrite(writefile, sprintf(`"Set objFiles = objFSO.GetFolder(strFolder).Files  \n"'))
	fwrite(writefile, sprintf(`"For Each objFile In objFiles  \n"'))
	fwrite(writefile, sprintf(`"    If Lcase(Split(objFile.Name, ".")(1)) = "xls" Then  \n"'))
	fwrite(writefile, sprintf(`"       Set objWorkbook = objExcel.Workbooks.Open(strFolder & objFile.Name) \n"'))
	fwrite(writefile, sprintf(`"       objWorkbook.SaveAs strFolder2 & Split(objFile.Name, ".")(0) & ".xlsx", 51  \n"'))
	fwrite(writefile, sprintf(`"       objWorkbook.Close  \n"'))
	fwrite(writefile, sprintf(`"    End If  \n"'))
    fwrite(writefile, sprintf(`"Next  \n"'))
    fwrite(writefile, sprintf(`"objExcel.Quit  \n"'))
	fclose(writefile)
}

void function writevbsfilexlsx(string scalar filename,string scalar filename2,string scalar todir)
{
	cc=`"""'	
	writefile=fopen("_xls__xlsx_.vbs","rw")
	fwrite(writefile, sprintf(`"Set objFSO = CreateObject("Scripting.FileSystemObject")  \n"'))
	fwrite(writefile, sprintf(`"Set objExcel = CreateObject("Excel.Application")  \n"'))
	fwrite(writefile, sprintf(`"objExcel.DisplayAlerts = False  \n"'))
	fwrite(writefile, sprintf(`"strFolder = %s%s%s  \n"',cc,filename,cc))
	fwrite(writefile, sprintf(`"strFolder2 =%s%s\%s  \n"',cc,todir,cc))
	fwrite(writefile, sprintf(`"filename2 = %s%s%s  \n"',cc,filename2,cc))
	fwrite(writefile, sprintf(`"Set objWorkbook = objExcel.Workbooks.Open(strFolder) \n"'))
	fwrite(writefile, sprintf(`"objWorkbook.SaveAs strFolder2 & Split(filename2, ".")(0) & ".xlsx", 51  \n"'))
	fwrite(writefile, sprintf(`"objWorkbook.Close  \n"'))
    fwrite(writefile, sprintf(`"objExcel.Quit  \n"'))
	fclose(writefile)
}





void function writevbsxls(string scalar path,string scalar todir)
{
	
	cc=`"""'
	writefile=fopen("_xls__xlsx_.vbs","rw")
	fwrite(writefile, sprintf(`"Set objFSO = CreateObject("Scripting.FileSystemObject")  \n"'))
	fwrite(writefile, sprintf(`"Set objExcel = CreateObject("Excel.Application")  \n"'))
	fwrite(writefile, sprintf(`"objExcel.DisplayAlerts = False  \n"'))
	fwrite(writefile, sprintf(`"strFolder = %s%s\%s  \n"',cc,path,cc))
	fwrite(writefile, sprintf(`"strFolder2 =%s%s\%s  \n"',cc,todir,cc))
	fwrite(writefile, sprintf(`"Set objFiles = objFSO.GetFolder(strFolder).Files  \n"'))
	fwrite(writefile, sprintf(`"For Each objFile In objFiles  \n"'))
	fwrite(writefile, sprintf(`"    If  Lcase(Split(objFile.Name, ".")(1)) = "xlsx" Then  \n"'))
	fwrite(writefile, sprintf(`"       Set objWorkbook = objExcel.Workbooks.Open(strFolder & objFile.Name) \n"'))
	fwrite(writefile, sprintf(`"       objWorkbook.SaveAs strFolder2 & Split(objFile.Name, ".")(0) & ".xls", 56  \n"'))
	fwrite(writefile, sprintf(`"       objWorkbook.Close  \n"'))
	fwrite(writefile, sprintf(`"    End If  \n"'))
    fwrite(writefile, sprintf(`"Next  \n"'))
    fwrite(writefile, sprintf(`"objExcel.Quit  \n"'))
	fclose(writefile)
}

void function writevbsfilexls(string scalar filename,string scalar filename2,string scalar todir)
{
	cc=`"""'	
	writefile=fopen("_xls__xlsx_.vbs","rw")
	fwrite(writefile, sprintf(`"Set objFSO = CreateObject("Scripting.FileSystemObject")  \n"'))
	fwrite(writefile, sprintf(`"Set objExcel = CreateObject("Excel.Application")  \n"'))
	fwrite(writefile, sprintf(`"objExcel.DisplayAlerts = False  \n"'))
	fwrite(writefile, sprintf(`"strFolder = %s%s%s  \n"',cc,filename,cc))
	fwrite(writefile, sprintf(`"strFolder2 =%s%s\%s  \n"',cc,todir,cc))
	fwrite(writefile, sprintf(`"filename2 = %s%s%s  \n"',cc,filename2,cc))
	fwrite(writefile, sprintf(`"Set objWorkbook = objExcel.Workbooks.Open(strFolder) \n"'))
	fwrite(writefile, sprintf(`"objWorkbook.SaveAs strFolder2 & Split(filename2, ".")(0) & ".xls", 56  \n"'))
	fwrite(writefile, sprintf(`"objWorkbook.Close  \n"'))
    fwrite(writefile, sprintf(`"objExcel.Quit  \n"'))
	fclose(writefile)
}





end
